package Action;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;




import Utils.Common;
import Utils.Constant;
import Utils.Download;
import Utils.FileUtils;
import Utils.GenUtils;
import Utils.MakeHTMLFile;
import Utils.ParseTotalPage;
import Utils.Random;
import Utils.StringUtil;
import Utils.Thumbnail;
import Utils.XmlReplace;


import Bean.NewsBean;
import Bean.NewsGroupBean;

public class News{
	public boolean insertNews(NewsBean newsBean, Connection conn){
		String sql = "INSERT INTO news (NewsId,Title,Summary,Content,Picture,Link,HadImg,DatePublished,Source,TempCat,Type,Thumbnail,Keyword,TotalNews,Poster,Status) "
			+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, newsBean.getNewsId());
			stmt.setString(2, newsBean.getTitle());
			stmt.setString(3, newsBean.getSummary());
			
			stmt.setString(4, newsBean.getContent());
			stmt.setString(5, newsBean.getPicture());
			stmt.setString(6, newsBean.getLink());
			stmt.setInt(7, newsBean.getHadImg());
			stmt.setString(8, convertDate(newsBean.getDatePublished()));
			stmt.setString(9, newsBean.getSource());
			stmt.setInt(10, newsBean.getTempCat());
			stmt.setInt(11, newsBean.getType());
			stmt.setString(12, newsBean.getThumbnail());
			stmt.setString(13, newsBean.getKeyword());
			stmt.setInt(14, newsBean.getTotalNews());
			stmt.setString(15, newsBean.getPoster());
			stmt.setInt(16, newsBean.getStatus());
			if(stmt.executeUpdate()>=1)
				return true;
			else return false;
		}catch (SQLException e) {
			return false;
		}finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
	}
	
	public boolean insertNewsGroup(NewsGroupBean newsBean, Connection conn){
		String sql = "INSERT INTO news_group (NewsId,Title,Summary,Link,DatePublished,Source,GroupId) "
			+ "VALUES (?,?,?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, newsBean.getNewsId());
			stmt.setString(2, newsBean.getTitle());
			stmt.setString(3, newsBean.getSummary());
			stmt.setString(4, newsBean.getLink());
			stmt.setString(5, convertDate(newsBean.getDatePublished()));
			stmt.setString(6, newsBean.getSource());
			stmt.setString(7, newsBean.getGroupId());
			if(stmt.executeUpdate()>=1)
				return true;
			else return false;
		}catch (SQLException e) {
//			e.printStackTrace();
			return false;
		}finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
	}
	
	
	
	public boolean updateNews(NewsBean newsBean, Connection conn, String userid, String usertype){
		String sql = "UPDATE news SET Title=?,Summary=?,Content=?,Picture=?,Link=?,HadImg=?," +
				"DatePublished=?,Source=?,Keyword=? WHERE NewsId=? ";
		if(!usertype.equals(Constant.AdminType)){
			sql = sql+" AND POSTER='"+userid+"'";
		}
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, newsBean.getTitle());
			stmt.setString(2, newsBean.getSummary());
			
			stmt.setString(3, newsBean.getContent());
			stmt.setString(4, newsBean.getPicture());
			stmt.setString(5, newsBean.getLink());
			stmt.setInt(6, newsBean.getHadImg());
			stmt.setString(7, convertDate(newsBean.getDatePublished()));
			stmt.setString(8, newsBean.getSource());
			stmt.setString(9, newsBean.getKeyword());
			stmt.setString(10, newsBean.getNewsId());			
			if(stmt.executeUpdate()>=1)
				return true;
			else return false;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
	}
	
	
	
	public boolean insertCat(String catidList, String newsId,Connection conn){
		PreparedStatement stmt = null;
		try{
			String sql = "DELETE FROM news_cat WHERE NewsId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newsId);
			stmt.executeUpdate();
			stmt.close();
			if(catidList.equals("")) return true;
			sql = "INSERT INTO news_cat values (?,?)";
			String []idlist = catidList.split(",");
			stmt = conn.prepareStatement(sql);
			for(int i=0; i < idlist.length; i++){
				stmt.setString(1, newsId);
				stmt.setString(2, idlist[i]);
				stmt.addBatch();
			}
			stmt.executeBatch();
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}finally{
			if(stmt!=null) try{stmt.close();}catch(Exception e){}
		}
		return true;
	}
	
	
	public String convertDate(String normalDate){
		String year = normalDate.split(" ")[0].split("/")[2];
		String month = normalDate.split(" ")[0].split("/")[1];
		String day = normalDate.split(" ")[0].split("/")[0];
		
		String hour = normalDate.split(" ")[1].split(":")[0];
		String minus = normalDate.split(" ")[1].split(":")[1];
		
		return year+"-"+month+"-"+day+" "+hour+":"+minus;
	}
	
	
	public String getListNews(HttpServletRequest req, Connection conn, String userid, String usertype) {
		// Fields
		String xmlResult = "";
		String sqlQuery = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		int page = Integer.parseInt(req.getParameter("page"))-1;
		int status = Integer.parseInt(req.getParameter("status"));
		int catid = Integer.parseInt(req.getParameter("catid"));
		
		try {
			if(catid==0){
				sqlQuery = "SELECT * FROM news  WHERE Status=? ";
			}else{
				sqlQuery = "SELECT * FROM news INNER JOIN news_cat ON news.NewsId=news_cat.NewsId " +
				"WHERE news_cat.CatId='"+catid+"' AND  news.Status=?";
			}
			
			if(!usertype.equals(Constant.AdminType))
				sqlQuery = sqlQuery+" AND Poster='"+userid+"' ";
			int total = 0;
			if (page <= -1) {
				total = getTotal(status,catid,userid,usertype,conn);
				xmlResult += "<page record=\""
						+ total
						+ "\" totalpage=\""
						+ ParseTotalPage
								.parse(total, 20)
						+ "\"/>";
				page = 0;
			}
			sqlQuery += " ORDER BY DatePublished DESC LIMIT ?,?";
			stmt = conn.prepareStatement(sqlQuery);
			stmt.setInt(1, status);
			stmt.setInt(2, page * 20);
			stmt.setInt(3, 20);
			rs = stmt.executeQuery();
			while (rs.next()) {
				xmlResult += "<news newsid=\"" + rs.getString("NewsId")
						+ "\" title=\""
						+ XmlReplace.escape(rs.getString("Title"))
						+ "\"  description=\""
						+ XmlReplace.escape(rs.getString("Summary"))
						+ "\" " 
						+ " thumbnail=\""
						+ XmlReplace.escape(rs.getString("Thumbnail"))
						+ "\" " 
						+ " poster=\""
						+ XmlReplace.escape(rs.getString("Poster"))
						+ "\" " 
						+ "createddate=\""
						+ XmlReplace.escape(rs.getString("DatePublished"))
						+ "\"/>";
			}
			

		} catch (SQLException e) {
			e.printStackTrace();
			return Common.makeErrorXML("");
		} catch (Exception re) {
			re.printStackTrace();
			return Common.makeErrorXML("");
		} finally{
			if(rs!=null)try{rs.close();}catch (Exception e) {}
			if(stmt!=null)try{stmt.close();}catch (Exception e) {}
		}
		return Common.makeSuccessXML(xmlResult);
	}
	public int getTotal(int status, int catid,String userid,String usertype, Connection conn) {
		String sqlQuery = "";
		if(catid==0){
			sqlQuery = "SELECT count(*) AS Total FROM news  WHERE  Status=? ";
		}else{
			sqlQuery = "SELECT count(*) AS Total FROM news INNER JOIN news_cat ON news.NewsId=news_cat.NewsId " +
			"WHERE news_cat.CatId='"+catid+"' AND  news.Status=?";
		}
		if(!usertype.equals(Constant.AdminType))
			sqlQuery = sqlQuery+" AND Poster='"+userid+"' ";
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			pre = conn.prepareStatement(sqlQuery);
			pre.setInt(1, status);
			rs = pre.executeQuery();

			while (rs.next()) {
				return Integer.parseInt(rs.getString("Total"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pre != null)
					pre.close();
			} catch (SQLException e) {
				
			}
		}
		return 0;
	}
	public String getNewsDetail(HttpServletRequest req, String userid,String usertype, Connection conn) throws Exception {
		String sqlQuery = null;
		String xmlResult = "";
		ResultSet rs = null;
		PreparedStatement stmt = null;
		String newsId = req.getParameter("newsid");
		sqlQuery = "SELECT * FROM news "
				+ "WHERE NewsId =?";
		if(!usertype.equals(Constant.AdminType))
			sqlQuery= sqlQuery+" AND Poster='"+userid+"'";
		try {
			stmt = conn.prepareStatement(sqlQuery);
			stmt.setString(1, newsId);
			rs = stmt.executeQuery();
			if (rs.next()) {
				NewsBean bean = new NewsBean();
				bean.setTitle(rs.getString("Title"));
				xmlResult += "<root value='true'><item newsid=\""
						+ rs.getString("NewsId") + "\" " + "title=\""
						+ XmlReplace.escape(rs.getString("Title")) + "\" "
						+ "description=\""
						+ XmlReplace.escape(rs.getString("Summary"))
						+ "\" " + "createddate=\""
						+ rs.getString("DatePublished") + "\" " + "content=\""
						+ XmlReplace.escape(rs.getString("Content")) + "\" "
						+ "picturefilename=\""
						+ XmlReplace.escape(rs.getString("Picture"))
						+ "\" " 
						+ "homepic=\""
						+ XmlReplace.escape(rs.getString("HomePic"))
						+ "\" " 
						+ "catid=\""+getCat(rs.getString("NewsId"),conn)+"\" "
						+ "source=\""+XmlReplace.escape(rs.getString("Source"))+"\" "
						+ "link=\""+XmlReplace.escape(rs.getString("Link"))+"\" "
						+ "keyword=\""+XmlReplace.escape(rs.getString("Keyword"))+"\" "
						+ "tempcat=\""+XmlReplace.escape(rs.getString("TempCat"))+"\" "
						+ "totalnews=\""+rs.getString("TotalNews")+"\" "
						+" thumbnail=\""
						+ rs.getString("Thumbnail") + "\" "
						+" utitle=\""
						+ XmlReplace.escape(bean.getUTitle()) + "\""
						+" poster=\""
						+ XmlReplace.escape(rs.getString("Poster")) + "\""
						+"/></root>";

			}else{
				xmlResult="<root value='empty'/>";
			}
			
		} catch (SQLException e) {
			return Common.makeErrorXML("");
		} catch (Exception e) {
			return Common.makeErrorXML("");
		} finally{
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				
			}
		}
		return xmlResult;
	}
	public String getCat(String newsid, Connection conn){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String ret="";
		String sql = "SELECT * FROM news_cat WHERE NewsId=?";
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newsid);
			rs = stmt.executeQuery();
			while(rs.next()){
				ret+=rs.getString("CatId")+",";
			}
			if(!ret.equals(""))
				ret = ret.substring(0, ret.length()-1);
		}catch (Exception e) {
			return "";
		}
		finally{
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				
			}
		}
		return ret;
	}
	public String deleteImg(HttpServletRequest req, Connection conn){
		String ret = "";
		String filename = req.getParameter("filename");
		String newsId = req.getParameter("newsid");
		String filelist = req.getParameter("filelist");
		String folder = GenUtils.genPathByAccountId(newsId);
		String file = folder+Constant.FILE_SEPARATOR+filename;
		
		File f = new File(file);
		if(f.exists()) f.delete();
		
		f = new File(file.replace(".", "thumb."));
		if(f.exists()) f.delete();
		
		String []flist=filelist.split(",");
		for(int i=0; i < flist.length; i++){
			if(!filename.equals(flist[i]))
				ret += flist[i]+",";
		}
		if(!ret.equals("")) ret = ret.substring(0,ret.length()-1);
		PreparedStatement stmt = null;
		try{
			String sql = "UPDATE news SET Picture=? WHERE NewsId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, ret);
			stmt.setString(2, newsId);
			stmt.executeUpdate();
		}catch (Exception e) {
			
		}finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return Common.makeSuccessXML("<ret value=\""+ret+"\"/>");
	}
	
	public String updateThumbnail(HttpServletRequest req, Connection conn, String userid, String usertype){
		String newsId = req.getParameter("newsid");
		String thumbnail = req.getParameter("filename");
//		String oldthumbnail = req.getParameter("oldfilename");
		PreparedStatement stmt = null;
		try{
			
			String sql = "UPDATE news SET Thumbnail=? WHERE NewsId=?";
			if(!usertype.equals(Constant.AdminType))
				sql = sql + " AND Poster='"+userid+"'";
			
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, thumbnail);
			stmt.setString(2, newsId);
			if(stmt.executeUpdate()>0){
//				String folder = GenUtils.genPathByAccountId(newsId);
//				String file = folder+Constant.FILE_SEPARATOR+oldthumbnail;
//				File f = new File(file);
//				if(f.exists()) f.delete();
//				
//				new Thumbnail().ThumbnailImage(folder+Constant.FILE_SEPARATOR+thumbnail,
//						folder +Constant.FILE_SEPARATOR+thumbnail.replace(".", "thumb."),
//						Constant.IMAGE_SMALL_THUMBHEIGHT,
//						Constant.IMAGE_SMALL_THUMBWIDTH);
			}
		}catch (Exception e) {
			return Common.makeErrorXML("");
		}finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return Common.makeSuccessXML();
	}
	public String deleteNews(HttpServletRequest req, Connection conn, String userid, String usertype){
		String newsId = req.getParameter("newsid");
		String sqlQuery = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			sqlQuery = "SELECT Thumbnail,HomePic,Picture FROM news WHERE NewsId=?";
			stmt = conn.prepareStatement(sqlQuery);
			stmt.setString(1, newsId);
			rs = stmt.executeQuery();
			String thumbnail = "",homepic = "",picture="";
			while(rs.next()){
				thumbnail = rs.getString("Thumbnail");
				homepic = rs.getString("HomePic");
				picture = rs.getString("Picture");
			}
			stmt.close();
			sqlQuery = "DELETE FROM news WHERE NewsId=?";
			if(!usertype.equals(Constant.AdminType))
				sqlQuery = sqlQuery + " AND Poster='"+userid+"'";
			stmt = conn.prepareStatement(sqlQuery);
			stmt.setString(1, newsId);
			if(stmt.executeUpdate()>0){
				String folder = GenUtils.genPathByAccountId(newsId);
				File f;
				if(thumbnail!=null && !thumbnail.equals("")){
					f = new File(folder+"/"+thumbnail);
					if(f.exists()) f.delete();
				}
				if(homepic!=null && !homepic.equals("")){
					f = new File(folder+"/"+homepic);
					if(f.exists()) f.delete();
				}
				String []arr = picture.split(",");
				for(int i=0; i < arr.length; i++){
					f = new File(folder+"/"+arr[i]);
					if(f.exists()) f.delete();
				}
			}
			stmt.close();
			sqlQuery = "DELETE FROM news_group WHERE GroupId=?";
			stmt = conn.prepareStatement(sqlQuery);
			stmt.setString(1, newsId);
			if(stmt.executeUpdate()>0){
				stmt.close();
				sqlQuery = "INSERT INTO tempid VALUES(?)";
				stmt = conn.prepareStatement(sqlQuery);
				stmt.setString(1, newsId);
				try{
					stmt.executeUpdate();
				}catch (Exception e) {
				}
			}
			return Common.makeSuccessXML();
		} catch (SQLException e) {
			e.printStackTrace();
			return Common.makeErrorXML("ERROR");
		} catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("ERROR");
		} finally{
			if(rs!=null) try{rs.close();}catch (Exception e) {}
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
	}
	public String updateStatus(HttpServletRequest req, Connection conn, String userid, String usertype){
		String newsId = req.getParameter("newsid");
		String status = req.getParameter("status");
		String sqlQuery = null;
		PreparedStatement stmt = null;
		try {
			sqlQuery = "UPDATE news SET Status=? WHERE NewsId=?";
			if(!usertype.equals(Constant.AdminType))
				sqlQuery = sqlQuery + " AND Poster='"+userid+"'";
			stmt = conn.prepareStatement(sqlQuery);
			stmt.setString(1, status);
			stmt.setString(2, newsId);
			stmt.executeUpdate();
			
			return Common.makeSuccessXML();
		} catch (SQLException e) {
			e.printStackTrace();
			return Common.makeErrorXML("ERROR");
		} catch (Exception e) {
			return Common.makeErrorXML("ERROR");
		} finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
	}
	
	public boolean updateTotalNews(int totalNews,String newsId, Connection conn){
		String sqlQuery = null;
		PreparedStatement stmt = null;
		try {
			sqlQuery = "UPDATE news SET TotalNews=? WHERE NewsId=?";
			stmt = conn.prepareStatement(sqlQuery);
			stmt.setInt(1, totalNews);
			stmt.setString(2, newsId);
			stmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			return false;
		} finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
	}
	
	
	public String insertHome(HttpServletRequest req,String userid, Connection conn){
		String newsId = req.getParameter("newsId");
		String catId = req.getParameter("catId");
		int type = Integer.parseInt(req.getParameter("type"));
		if(insertHomeFromId(newsId,catId,type,userid, conn))
			return Common.makeSuccessXML();
		else return Common.makeErrorXML("Error");
	}
	public boolean insertHomeFromId(String newsId,String catId,int type,String userid, Connection conn){
		String sql = "SELECT DatePublished FROM news WHERE NewsId=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String date = "";
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newsId);
			rs = stmt.executeQuery();
			if(rs.next()){
				date = rs.getString("DatePublished");
				stmt.close();
				if(type==1){
					sql = "UPDATE home_"+catId+" SET `Position`=(1+`Position`)";
					stmt = conn.prepareStatement(sql);
					stmt.executeUpdate();
					stmt.close();
					
					sql = "INSERT INTO home_"+catId+" (`NewsId`,`Position`,`DatePublished`,`Poster`) VALUES (?,1,?,?)";
					stmt = conn.prepareStatement(sql);
					stmt.setString(1, newsId);
					stmt.setString(2, date);
					stmt.setString(3, userid);
					stmt.executeUpdate();
				}else{
					sql = "SELECT max(Position) AS m FROM home_"+catId;
					stmt = conn.prepareStatement(sql);
					rs = stmt.executeQuery();
					int pos = 1;
					if(rs.next())
						pos = rs.getInt("m")+1;
					
					sql = "INSERT INTO home_"+catId+" (`NewsId`,`Position`,`DatePublished`,`Poster`) VALUES (?,"+pos+",?,?)";
					stmt = conn.prepareStatement(sql);
					stmt.setString(1, newsId);
					stmt.setString(2, date);
					stmt.setString(3, userid);
					stmt.executeUpdate();
					stmt.close();
				}
				return true;
			}
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return true;
	}
	public String listHome(String catId, Connection conn){
		
		String sql = "SELECT * FROM news INNER JOIN home_"+catId+" ON news.NewsId=home_"+catId+".NewsId " +
				" ORDER BY home_"+catId+".Position ASC";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xmlResult = "";
		
		try{
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				NewsBean bean = new NewsBean();
				bean.setTitle(rs.getString("Title"));
				xmlResult += "<news newsid=\"" + rs.getString("NewsId")
						+ "\" thumbnail=\""+rs.getString("Thumbnail")+"\" "
						+ "pos=\""
						+ rs.getString("Position")
						+ "\" " 
						+ "createddate=\""
						+ rs.getString("DatePublished")
						+ "\" " 
						+ "homepic=\""
						+ rs.getString("HomePic")
						+ "\" "
						+ "poster=\""
						+ rs.getString("home_"+catId+".Poster")
						+ "\" "
						+ "link=\""
						+ XmlReplace.escape(rs.getString("Link"))
						+ "\" " 
						+ "title=\""
						+ XmlReplace.escape(rs.getString("Title"))
						+ "\"  description=\""
						+ XmlReplace.escape(rs.getString("Summary"))
						+ "\" "
						+ "utitle=\""
						+ XmlReplace.escape(bean.getUTitle())
						+ "\"/>";
			}
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("ERROR");
		} finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return Common.makeSuccessXML(xmlResult);
	}
	public String deleteHome(HttpServletRequest req, Connection conn){
		String newsId = req.getParameter("newsId");
		String catId = req.getParameter("catId");
		String sql = "DELETE FROM home_"+catId+" WHERE NewsId=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newsId);
			stmt.executeUpdate();
		}catch (Exception e) {
			
			return Common.makeErrorXML("ERROR");
		} finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return Common.makeSuccessXML();
	}
	
	public String updatePosition(HttpServletRequest req, Connection conn){
		String idlist = req.getParameter("idList");
		String catId = req.getParameter("catId");
		String []idlist_ = idlist.split(",");
		String sql = "UPDATE home_"+catId+" SET `Position`=(500+`Position`)";
		PreparedStatement stmt =  null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();
			stmt.close();
			sql = "UPDATE home_"+catId+" SET `Position`=? WHERE NewsId=?";
			stmt = conn.prepareStatement(sql);
			for(int i=0; i < idlist_.length; i++ ){
				stmt.setString(1, idlist_[i].split("-")[1]);
				stmt.setString(2, idlist_[i].split("-")[0]);
				stmt.addBatch();
			}
			stmt.executeBatch();
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("ERROR");
		}finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return Common.makeSuccessXML();
	}
	
//	public String updateModeCat(HttpServletRequest req, Connection conn){
//		String status = req.getParameter("status");
//		String catId = req.getParameter("catId");
//		String sql = "UPDATE cat SET `Mode`=? WHERE `CatId`=?";
//		PreparedStatement stmt =  null;
//		try{
//			stmt = conn.prepareStatement(sql);
//			stmt.setString(1, status);
//			stmt.setString(2, catId);
//			stmt.executeUpdate();
//			Download.reload(Constant.reloadTOPNEWSCAT_URL+catId);
//		}catch (Exception e) {
//			e.printStackTrace();
//			return Common.makeErrorXML("ERROR");
//		}finally{
//			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
//		}
//		return Common.makeSuccessXML();
//	}
//	
//	public String getModeCat(HttpServletRequest req, Connection conn){
//		String catId = req.getParameter("catId");
//		String sql = "SELECT * FROM cat WHERE `CatId`=?";
//		PreparedStatement stmt =  null;
//		ResultSet rs = null;
//		try{
//			stmt = conn.prepareStatement(sql);
//			stmt.setString(1, catId);
//			rs = stmt.executeQuery();
//			while(rs.next()){
//				return Common.makeSuccessXML("<cat mode='"+rs.getString("Mode")+"'/>");
//			}
//		}catch (Exception e) {
//			e.printStackTrace();
//			return Common.makeErrorXML("ERROR");
//		}finally{
//			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
//		}
//		return Common.makeSuccessXML();
//	}
	
	public String deleteHomePic(HttpServletRequest req, Connection conn){
		String newsId = req.getParameter("newsid");
		String filedeleted = req.getParameter("filename");
		String oldhomepic = req.getParameter("oldFile");
		PreparedStatement stmt = null;
		String newfile = "";
		try{
			String filelist[] = oldhomepic.split(",");
			for(int i=0; i < filelist.length; i++){
				if(!filedeleted.equals(filelist[i]))
					newfile+=filelist[i]+",";
			}
			
			if(!newfile.equals(""))
				newfile = newfile.substring(0,newfile.length()-1);
				
			String sql = "UPDATE news SET HomePic=? WHERE NewsId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newfile);
			stmt.setString(2, newsId);
			if(stmt.executeUpdate()>0){
				String folder = GenUtils.genPathByAccountId(newsId);
				String file = folder+Constant.FILE_SEPARATOR+filedeleted;
				
				File f = new File(file);
				if(f.exists())
					f.delete();
			}
		}catch (Exception e) {
			return Common.makeErrorXML("");
		}finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return Common.makeSuccessXML("<home img=\""+newfile+"\"/>");
	}
	public String listNewsCat(HttpServletRequest req, Connection conn){
		int n = Integer.parseInt(req.getParameter("n"));
		int page = Integer.parseInt(req.getParameter("page"))-1;
		int catid =Integer.parseInt(req.getParameter("catid"));
		String sql = "SELECT * FROM news INNER JOIN news_cat ON news.NewsId=news_cat.NewsId " +
				"WHERE news.Status=1 AND news_cat.CatId=? ORDER BY news.DatePublished DESC LIMIT ?,?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xml = "";
		try{
			int total = 0;
			if (page <= -1) {
				total = listTotalNewsCat(catid,conn);
				xml += "<page record=\""
						+ total
						+ "\" totalpage=\""
						+ ParseTotalPage
								.parse(total, n)
						+ "\"/>";
				page = 0;
			}
			
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, catid);
			stmt.setInt(2, n*page);
			stmt.setInt(3, n);
			rs = stmt.executeQuery();
			while(rs.next()){
				NewsBean bean = new NewsBean();
				bean.setTitle(rs.getString("Title"));
				xml += "<item newsid=\""
					+ rs.getString("NewsId") + "\" " + "title=\""
					+ XmlReplace.escape(rs.getString("Title")) + "\" "
					+ "description=\""
					+ XmlReplace.escape(rs.getString("Summary"))
					+ "\" " + "createddate=\""
					+ rs.getString("DatePublished") + "\" "
					+ "source=\""+XmlReplace.escape(rs.getString("Source"))+"\" "
					+ "homepic=\""+XmlReplace.escape(rs.getString("HomePic"))+"\" "
					+ "link=\""+XmlReplace.escape(rs.getString("Link"))+"\" "
					+ "thumbnail=\""
					+ rs.getString("Thumbnail") + "\" "
					+ "utitle=\""+XmlReplace.escape(bean.getUTitle())+"\" "+
					"/>";
			}
			return Common.makeSuccessXML(xml);
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null) try{rs.close();}catch (Exception e) {}
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		
	}
	public int  listTotalNewsCat(int catid, Connection conn){
		String sql = "SELECT COUNT(news.NewsId) FROM news INNER JOIN news_cat ON news.NewsId=news_cat.NewsId " +
				"WHERE news.Status=1 AND news_cat.CatId=? ";		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, catid);
			rs = stmt.executeQuery();
			while(rs.next()){
				return rs.getInt(1);
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			return -1;
		}finally{
			if(rs!=null) try{rs.close();}catch (Exception e) {}
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
		return -1;
	}
	public boolean publishHome(String catId, Connection conn){
		try{conn.close();}catch (Exception e) {	}
		Download.reload(Constant.reloadTOPNEWS_URL+"mainhome_"+catId);
		Download.reload(Constant.reloadTOPNEWS_URL+"maincat_"+catId);
		return true;
	}
	
	public boolean clearAllCache(){
		String catid[] = {"1","2","3","4","5","6","7","8","9"};
		for(int i=0; i < catid.length; i++){
			Download.reload(Constant.reloadTOPNEWS_URL+"cathomelist_"+catid[i]);
			Download.reload(Constant.reloadTOPNEWS_URL+"cathome_"+catid[i]);
		}
		return true;
	}
	
	
	public String[] getImg(String content,String newsId,Connection conn){
		ArrayList<String> arr = new ArrayList<String>();
		String r[]={"",""};
		String xml_content = "<xml>"+content+"</xml>";
		String filepath = Constant.FOLDER_DATA+"/"+Random.randomString(4); 
		FileUtils.writeFile(xml_content, filepath, "UTF-8");
		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		try{
			MakeHTMLFile.MakePrettyHTMLFile(filepath, filepath);
			DocumentBuilder db = dbf.newDocumentBuilder();
			Document doc = db.parse(new File(filepath));
			Element elem = doc.getDocumentElement();
			NodeList nl = elem.getElementsByTagName("img");
			for(int i=0; i < nl.getLength(); i++){
				Node n = nl.item(i);
				Element ele = (Element) n;
				String src = ele.getAttribute("src");
				
				/****Download Picture****/
				String path = GenUtils.genPathByAccountId(newsId);
				String imageName = Random.randomString(8);
				if(src.indexOf("rolo.vn")<0 && src.indexOf("localhost")<0){
					imageName = Download.download(src, path,imageName);
					String sourceFile = path+"/"+imageName;
					new Thumbnail().ThumbnailImage(sourceFile, sourceFile, 800, 800);
					if(!imageName.equals(""))
						arr.add(src+"@VTC@"+imageName);
				}
				/************************/
			}
			String fileres="";
			for(int i=0; i < arr.size();i++){
				String filename = arr.get(i).split("@VTC@")[1];
				String source = arr.get(i).split("@VTC@")[0];
				content = content.replace(source, GenUtils.genLinkImg(newsId, filename));
				fileres += filename+",";
			}
			if(!fileres.equals("")) fileres = fileres.substring(0,fileres.length() - 1);
			arr.clear();
			r[0]=content;r[1] = fileres;
			return r;
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			File f1 = new File(filepath);
			if(f1.exists()) f1.delete();
		}
		return null;
	}
	public String genContentForEmail(HttpServletRequest req, Connection conn){
		String linklist = req.getParameter("link");
		String a[]=linklist.split(",");
		String content = FileUtils.readFile(Constant.FOLDER_DATA+"/r_email_marketing.html");
		ArrayList<NewsBean> bean = new ArrayList<NewsBean>();
		try{
			for(int i=0; i < a.length; i++){
				String id = a[i].split("\\/")[5];
				NewsBean nbean = new NewsBean();
				nbean = new News().getNewsDetailBean(id, conn);
				bean.add(nbean);
			}
			for(int i=0; i < bean.size(); i++){
				if(bean!=null){
					content = content.replace("$TITLE_"+i, bean.get(i).getTitle());
					content = content.replace("$LINK_"+i, "http://art.rolo.vn/a/"+GenUtils.genLinkView(bean.get(i).getNewsId(), bean.get(i).getUTitle()));
					content = content.replace("$SUM_"+i, bean.get(i).getSummary());
					String img = "";
					if(i==0){
						img = GenUtils.genLinkImg(bean.get(i).getNewsId(), bean.get(i).getHomePic());
					}else{
						img = GenUtils.genLinkImg(bean.get(i).getNewsId(), bean.get(i).getThumbnail());
					}
					content = content.replace("$THUMB_"+i, img);
				}
			}
			return content;
		}catch (Exception e) {
			return "Error";
		}
	}
	public NewsBean getNewsDetailBean(String id, Connection conn){
		ResultSet rs = null;
		PreparedStatement stmt = null;
		String sqlQuery = "SELECT * FROM news "
				+ "WHERE NewsId =?";

		try {
			stmt = conn.prepareStatement(sqlQuery);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				NewsBean bean = new NewsBean();
				bean.setTitle(rs.getString("Title"));
				bean.setHomePic(rs.getString("HomePic"));
				bean.setDatePublished(rs.getString("DatePublished"));
				bean.setSource(rs.getString("Source"));
				bean.setNewsId(rs.getString("NewsId"));
				bean.setSummary(rs.getString("Summary"));
				bean.setThumbnail(rs.getString("Thumbnail"));
				bean.setLink(rs.getString("Link"));
				bean.setTotalNews(rs.getInt("TotalNews"));
				return bean;
			}else{
				return null;
			}
			
		} catch (SQLException e) {
			return null;
		} catch (Exception e) {
			return null;
		} finally{
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				
			}
		}
	}
	public String listNewsInGroup(String groupId, Connection conn){
		String sql = "SELECT * FROM news_group WHERE GroupId=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xml = "";
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, groupId);
			rs = stmt.executeQuery();
			while(rs.next()){
				String t= StringUtil.convertDate(rs.getString("DatePublished"),"vn");
				t = t.split(", ")[1]+" "+t.split(", ")[0];
				xml+="<news title=\""+XmlReplace.escape(rs.getString("Title"))+"\" "+
							"newsid=\""+rs.getString("NewsId")+"\" "+
							"link=\""+XmlReplace.escape(rs.getString("Link"))+"\" "+
							"date=\""+XmlReplace.escape(t)+"\" "+
							"source=\""+XmlReplace.escape(rs.getString("Source"))+"\" />";
			}
			if(xml.equals("")){
				NewsBean bean = new News().getNewsDetailBean(groupId, conn);
				NewsGroupBean newsgroup = new NewsGroupBean();
				newsgroup.setNewsId(groupId);
				newsgroup.setTitle(bean.getTitle());
				newsgroup.setSummary(bean.getSummary());
				newsgroup.setLink(bean.getLink());
				newsgroup.setSource(bean.getSource());
				String t = StringUtil.convertDate(bean.getDatePublished(),"vn");
				newsgroup.setDatePublished(t.split(", ")[1]+" "+t.split(", ")[0]);
				newsgroup.setGroupId(groupId);
				if(new News().insertNewsGroup(newsgroup, conn)){
					xml="<news title=\""+XmlReplace.escape(newsgroup.getTitle())+"\" "+
					"newsid=\""+newsgroup.getNewsId()+"\" "+
					"link=\""+XmlReplace.escape(newsgroup.getLink())+"\" "+
					"date=\""+XmlReplace.escape(t)+"\" "+
					"source=\""+XmlReplace.escape(newsgroup.getSource())+"\" />";
					return Common.makeSuccessXML(xml);
				}else
					return Common.makeErrorXML("Error");
			}else{
				return Common.makeSuccessXML(xml);
			}
				
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			try{
				if(rs!=null)rs.close();
				if(stmt!=null)stmt.close();
			}catch (Exception e) {
			}
		}
	}
	
	
	public String deleteNewsInGroup(String newsId,String groupId, Connection conn){
		String sql = "DELETE FROM news_group WHERE NewsId=? AND GroupId=?";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newsId);
			stmt.setString(2, groupId);
			if(stmt.executeUpdate()>0){
				NewsBean bean = getNewsDetailBean(groupId, conn);
				int total = bean.getTotalNews();
				total = Math.max(total-1,1);
				updateTotalNews(total, groupId, conn);
			}
			
			return Common.makeSuccessXML();
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			try{
				if(stmt!=null)stmt.close();
			}catch (Exception e) {
			}
		}
	}
	
	public String addNewsToGroup(HttpServletRequest req, Connection conn){
		String title = req.getParameter("title");
		String link =  req.getParameter("link");
		String date = req.getParameter("date");
		String groupId = req.getParameter("groupId");
		String source = req.getParameter("source");
		NewsGroupBean bean = new NewsGroupBean();
		bean.setNewsId(Random.randomNumber(12));
		bean.setTitle(title);
		bean.setLink(link);
		bean.setDatePublished(date);
		bean.setSummary("");
		bean.setSource(source);
		bean.setGroupId(groupId);
		if(insertNewsGroup(bean, conn)){
			NewsBean bean2 = getNewsDetailBean(groupId, conn);
			int total = bean2.getTotalNews();
			updateTotalNews(total+1, groupId, conn);
			return Common.makeSuccessXML();
		}else{
			return Common.makeErrorXML("Error");
		}
	}
	
	public String updateNewsInGroup(HttpServletRequest req, Connection conn){
		String title = req.getParameter("title");
		String link =  req.getParameter("link");
		String date = req.getParameter("date");
		String groupId = req.getParameter("groupId");
		String source = req.getParameter("source");
		String newsId = req.getParameter("newsId");
		PreparedStatement stmt = null;
		try{
			String sql = "UPDATE news_group SET Title=?, Link=?,DatePublished=?,Source=? WHERE NewsId=? AND GroupId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, title);
			stmt.setString(2, link);
			stmt.setString(3, convertDate(date));
			stmt.setString(4, source);
			stmt.setString(5, newsId);
			stmt.setString(6, groupId);
			stmt.executeUpdate();
			return Common.makeSuccessXML();
		}catch (Exception e) {
				e.printStackTrace();
				return Common.makeErrorXML("Error");
		}finally{
			try{
				if(stmt!=null)stmt.close();
			}catch (Exception e) {
			}
		}
	}
	
	public String changeTopNewsToLatest(HttpServletRequest req, Connection conn){
		String latesId = req.getParameter("latestId");
		NewsGroupBean bean = new NewsGroupBean();
		bean.setNewsId(latesId);
		bean.setTitle(req.getParameter("title"));
		bean.setDatePublished(req.getParameter("date"));
		bean.setLink(req.getParameter("link"));
		bean.setSource(req.getParameter("source"));
		bean.setSummary(req.getParameter("sum"));
		bean.setGroupId(latesId);
		String topId = req.getParameter("topId");
		int total = Integer.parseInt(req.getParameter("total"));
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			insertNewsGroup(bean, conn);
			new AutoNews().insertNewsGroup(topId, latesId, conn);
			
			String sql = "SELECT COUNT(*) AS t FROM news_group WHERE GroupId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, latesId);
			rs = stmt.executeQuery();
			if(rs.next()){
				total = rs.getInt("t");
				sql = "UPDATE news SET TotalNews=? WHERE NewsId=?";
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, total);
				stmt.setString(2, latesId);
				if(stmt.executeUpdate()>0){
					stmt.close();
					sql = "INSERT INTO tempid VALUES(?)";
					stmt = conn.prepareStatement(sql);
					stmt.setString(1, topId);
					try{stmt.executeUpdate();
					}catch (Exception e) {
						// TODO: handle exception
					}
				}
			}
			
			return Common.makeSuccessXML();
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null)try{rs.close();}catch (Exception e) {}
			if(stmt!=null)try{stmt.close();}catch (Exception e) {}
		}
	}
	public String resetDatePublished(Connection conn){
		String sql = "update news set DatePublished=now() where DatePublished='0000-00-00 00:00:00'";
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();
			return Common.makeErrorXML("Success");
		} catch (SQLException e) {
			return Common.makeErrorXML("Error");
		} finally{
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				
			}
		}
	}
}